package com.aixin.csopuser.repository;

import com.aixin.csopuser.domain.CustomerEquity;
import com.aixin.csopuser.domain.SupplierCustomerDomain;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Optional;

public interface CustomerEquityRepository extends JpaRepository<CustomerEquity, Long>, JpaSpecificationExecutor<CustomerEquity> {

    @Query(value = "select new com.aixin.csopuser.domain.CustomerEquity(sld.equityLevelID) " +
            "from ServiceLevelDef sld inner join ServiceDef sd " +
            "on sld.serviceID=sd.serviceID " +
            "where 1=1 and (:supplierID is null or sd.supplierID=:supplierID) " +
            "and (:serviceID is null or sd.serviceID=:serviceID) group by sld.equityLevelID")
    List<CustomerEquity> getEquityLevelIDBySupplier(@Param("supplierID") String supplierID, @Param("serviceID") String serviceID);

    @Query(value = "select new com.aixin.csopuser.domain.SupplierCustomerDomain(ce.id, ce.customerID," +
            "       ce.policyNumber," +
            "       c.custName," +
            "       c.certID," +
            "       c.phoneNumber," +
            "       ce.usedTIme," +
            "       ce.effStrTime," +
            "       ce.custLevelID," +
            "       ce.equityID," +
            "       c.gender," +
            "       ce.usedNumber) " +
            " from CustomerEquity ce JOIN CustomerDomain c ON c.customerID = ce.customerID " +
            " where 1=1 " +
            " and ((:custName is null) or (c.custName like concat('%',:custName,'%')))" +
            " and ((:certID is null) or (c.certID like concat('%',:certID,'%')))" +
            " and ((:useIdList is null) or (ce.equityID in :equityIDs))")
    Page<SupplierCustomerDomain> getCustomerEquityBySupplier(@Param("custName") String custName,
                                                             @Param("certID") String certID,
                                                             @Param("useIdList") String useIdList,
                                                             @Param("equityIDs") List<String> equityIDs,
                                                             Pageable pageable);

    List<CustomerEquity> findAllByPolicyNumber(String policyNumber);

    Optional<CustomerEquity> findById(String id);
}
